﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace YJ.Data.ORACLE
{
    public class ProgramBuilder: YJ.Data.Interface.IProgramBuilder
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public ProgramBuilder()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.ProgramBuilder实体类</param>
        /// <returns>操作所影响的行数</returns>
        public int Add(YJ.Data.Model.ProgramBuilder model)
        {
            string sql = @"INSERT INTO ProgramBuilder
				(ID,Name,Type,CreateTime,PublishTime,CreateUser,SQL,IsAdd,DBConnID,Status,FormID,EditModel,Width,Height,ButtonLocation,IsPager,ClientScript,ExportTemplate,ExportHeaderText,ExportFileName,TableStyle,TableHead,TableName,InDataNumberFiledName) 
				VALUES(:ID,:Name,:Type,:CreateTime,:PublishTime,:CreateUser,:SQL,:IsAdd,:DBConnID,:Status,:FormID,:EditModel,:Width,:Height,:ButtonLocation,:IsPager,:ClientScript,:ExportTemplate,:ExportHeaderText,:ExportFileName,:TableStyle,:TableHead,:TableName,:InDataNumberFiledName)";
            OracleParameter[] parameters = new OracleParameter[]{
				new OracleParameter(":ID", OracleDbType.Varchar2){ Value = model.ID },
				new OracleParameter(":Name", OracleDbType.Varchar2){ Value = model.Name },
				new OracleParameter(":Type", OracleDbType.Varchar2){ Value = model.Type },
				new OracleParameter(":CreateTime", OracleDbType.Date){ Value = model.CreateTime },
				model.PublishTime == null ? new OracleParameter(":PublishTime", OracleDbType.Date) { Value = DBNull.Value } : new OracleParameter(":PublishTime", OracleDbType.Date) { Value = model.PublishTime },
				new OracleParameter(":CreateUser", OracleDbType.Varchar2){ Value = model.CreateUser },
				new OracleParameter(":SQL", OracleDbType.Varchar2){ Value = model.SQL },
				new OracleParameter(":IsAdd", OracleDbType.Int32){ Value = model.IsAdd },
				new OracleParameter(":DBConnID", OracleDbType.Varchar2){ Value = model.DBConnID },
				new OracleParameter(":Status", OracleDbType.Int32){ Value = model.Status },
				model.FormID == null ? new OracleParameter(":FormID", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":FormID", OracleDbType.Varchar2) { Value = model.FormID },
				model.EditModel == null ? new OracleParameter(":EditModel", OracleDbType.Int32) { Value = DBNull.Value } : new OracleParameter(":EditModel", OracleDbType.Int32) { Value = model.EditModel },
				model.Width == null ? new OracleParameter(":Width", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":Width", OracleDbType.Varchar2) { Value = model.Width },
				model.Height == null ? new OracleParameter(":Height", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":Height", OracleDbType.Varchar2) { Value = model.Height },
				model.ButtonLocation == null ? new OracleParameter(":ButtonLocation", OracleDbType.Int32) { Value = DBNull.Value } : new OracleParameter(":ButtonLocation", OracleDbType.Int32) { Value = model.ButtonLocation },
				model.IsPager == null ? new OracleParameter(":IsPager", OracleDbType.Int32) { Value = DBNull.Value } : new OracleParameter(":IsPager", OracleDbType.Int32) { Value = model.IsPager },
				model.ClientScript == null ? new OracleParameter(":ClientScript", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":ClientScript", OracleDbType.Varchar2) { Value = model.ClientScript },
                model.ExportTemplate == null ? new OracleParameter(":ExportTemplate", OracleDbType.Varchar2, 4000) { Value = DBNull.Value } : new OracleParameter(":ExportTemplate", OracleDbType.Varchar2, 4000) { Value = model.ExportTemplate },
				model.ExportHeaderText == null ? new OracleParameter(":ExportHeaderText", OracleDbType.NVarchar2, 500) { Value = DBNull.Value } : new OracleParameter(":ExportHeaderText", OracleDbType.NVarchar2, 500) { Value = model.ExportHeaderText },
				model.ExportFileName == null ? new OracleParameter(":ExportFileName", OracleDbType.Varchar2, 500) { Value = DBNull.Value } : new OracleParameter(":ExportFileName", OracleDbType.Varchar2, 500) { Value = model.ExportFileName },
                model.TableStyle == null ? new OracleParameter(":TableStyle", OracleDbType.Varchar2, 255) { Value = DBNull.Value } : new OracleParameter(":TableStyle", OracleDbType.Varchar2, 255) { Value = model.TableStyle },
                model.TableHead == null ? new OracleParameter(":TableHead", OracleDbType.Varchar2, 4000) { Value = DBNull.Value } : new OracleParameter(":TableHead", OracleDbType.Varchar2, 4000) { Value = model.TableHead },
                model.TableName == null ? new OracleParameter(":TableName", OracleDbType.Varchar2, 500) { Value = DBNull.Value } : new OracleParameter(":TableName", OracleDbType.Varchar2, 500) { Value = model.TableName },
                model.InDataNumberFiledName == null ? new OracleParameter(":InDataNumberFiledName", OracleDbType.Varchar2, 500) { Value = DBNull.Value } : new OracleParameter(":InDataNumberFiledName", OracleDbType.Varchar2, 500) { Value = model.InDataNumberFiledName }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.ProgramBuilder实体类</param>
        public int Update(YJ.Data.Model.ProgramBuilder model)
        {
            string sql = @"UPDATE ProgramBuilder SET 
				Name=:Name,Type=:Type,CreateTime=:CreateTime,PublishTime=:PublishTime,CreateUser=:CreateUser,SQL=:SQL,IsAdd=:IsAdd,DBConnID=:DBConnID,Status=:Status,FormID=:FormID,EditModel=:EditModel,Width=:Width,Height=:Height,ButtonLocation=:ButtonLocation,IsPager=:IsPager,ClientScript=:ClientScript,ExportTemplate=:ExportTemplate,ExportHeaderText=:ExportHeaderText,ExportFileName=:ExportFileName,TableStyle=:TableStyle,TableHead=:TableHead,TableName=:TableName,InDataNumberFiledName=:InDataNumberFiledName   
				WHERE ID=:ID";
            OracleParameter[] parameters = new OracleParameter[]{
				new OracleParameter(":Name", OracleDbType.Varchar2){ Value = model.Name },
				new OracleParameter(":Type", OracleDbType.Varchar2){ Value = model.Type },
				new OracleParameter(":CreateTime", OracleDbType.Date){ Value = model.CreateTime },
				model.PublishTime == null ? new OracleParameter(":PublishTime", OracleDbType.Date) { Value = DBNull.Value } : new OracleParameter(":PublishTime", OracleDbType.Date) { Value = model.PublishTime },
				new OracleParameter(":CreateUser", OracleDbType.Varchar2){ Value = model.CreateUser },
				new OracleParameter(":SQL", OracleDbType.Varchar2){ Value = model.SQL },
				new OracleParameter(":IsAdd", OracleDbType.Int32){ Value = model.IsAdd },
				new OracleParameter(":DBConnID", OracleDbType.Varchar2){ Value = model.DBConnID },
				new OracleParameter(":Status", OracleDbType.Int32){ Value = model.Status },
				model.FormID == null ? new OracleParameter(":FormID", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":FormID", OracleDbType.Varchar2) { Value = model.FormID },
				model.EditModel == null ? new OracleParameter(":EditModel", OracleDbType.Int32) { Value = DBNull.Value } : new OracleParameter(":EditModel", OracleDbType.Int32) { Value = model.EditModel },
				model.Width == null ? new OracleParameter(":Width", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":Width", OracleDbType.Varchar2) { Value = model.Width },
				model.Height == null ? new OracleParameter(":Height", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":Height", OracleDbType.Varchar2) { Value = model.Height },
				model.ButtonLocation == null ? new OracleParameter(":ButtonLocation", OracleDbType.Int32) { Value = DBNull.Value } : new OracleParameter(":ButtonLocation", OracleDbType.Int32) { Value = model.ButtonLocation },
				model.IsPager == null ? new OracleParameter(":IsPager", OracleDbType.Int32) { Value = DBNull.Value } : new OracleParameter(":IsPager", OracleDbType.Int32) { Value = model.IsPager },
				model.ClientScript == null ? new OracleParameter(":ClientScript", OracleDbType.Varchar2) { Value = DBNull.Value } : new OracleParameter(":ClientScript", OracleDbType.Varchar2) { Value = model.ClientScript },
                model.ExportTemplate == null ? new OracleParameter(":ExportTemplate", OracleDbType.Varchar2, 4000) { Value = DBNull.Value } : new OracleParameter(":ExportTemplate", OracleDbType.Varchar2, 4000) { Value = model.ExportTemplate },
				model.ExportHeaderText == null ? new OracleParameter(":ExportHeaderText", OracleDbType.NVarchar2, 500) { Value = DBNull.Value } : new OracleParameter(":ExportHeaderText", OracleDbType.NVarchar2, 500) { Value = model.ExportHeaderText },
				model.ExportFileName == null ? new OracleParameter(":ExportFileName", OracleDbType.Varchar2, 500) { Value = DBNull.Value } : new OracleParameter(":ExportFileName", OracleDbType.Varchar2, 500) { Value = model.ExportFileName },
                model.TableStyle == null ? new OracleParameter(":TableStyle", OracleDbType.Varchar2, 255) { Value = DBNull.Value } : new OracleParameter(":TableStyle", OracleDbType.Varchar2, 255) { Value = model.TableStyle },
                model.TableHead == null ? new OracleParameter(":TableHead", OracleDbType.Varchar2, 4000) { Value = DBNull.Value } : new OracleParameter(":TableHead", OracleDbType.Varchar2, 4000) { Value = model.TableHead },
                model.TableName == null ? new OracleParameter(":TableName", OracleDbType.Varchar2, 500) { Value = DBNull.Value } : new OracleParameter(":TableName", OracleDbType.Varchar2, 500) { Value = model.TableName },
                model.InDataNumberFiledName == null ? new OracleParameter(":InDataNumberFiledName", OracleDbType.Varchar2, 500) { Value = DBNull.Value } : new OracleParameter(":InDataNumberFiledName", OracleDbType.Varchar2, 500) { Value = model.InDataNumberFiledName },
				new OracleParameter(":ID", OracleDbType.Varchar2){ Value = model.ID }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(Guid id)
        {
            string sql = "DELETE FROM ProgramBuilder WHERE ID=:ID";
            OracleParameter[] parameters = new OracleParameter[]{
				new OracleParameter(":ID", OracleDbType.Varchar2){ Value = id }
			};
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<YJ.Data.Model.ProgramBuilder> DataReaderToList(OracleDataReader dataReader)
        {
            List<YJ.Data.Model.ProgramBuilder> List = new List<YJ.Data.Model.ProgramBuilder>();
            YJ.Data.Model.ProgramBuilder model = null;
            while (dataReader.Read())
            {
                model = new YJ.Data.Model.ProgramBuilder();
                model.ID = dataReader.GetString(0).ToGuid();
                model.Name = dataReader.GetString(1);
                model.Type = dataReader.GetString(2).ToGuid();
                model.CreateTime = dataReader.GetDateTime(3);
                if (!dataReader.IsDBNull(4))
                    model.PublishTime = dataReader.GetDateTime(4);
                model.CreateUser = dataReader.GetString(5).ToGuid();
                model.SQL = dataReader.GetString(6);
                model.IsAdd = dataReader.GetInt32(7);
                model.DBConnID = dataReader.GetString(8).ToGuid();
                model.Status = dataReader.GetInt32(9);
                if (!dataReader.IsDBNull(10))
                    model.FormID = dataReader.GetString(10);
                if (!dataReader.IsDBNull(11))
                    model.EditModel = dataReader.GetInt32(11);
                if (!dataReader.IsDBNull(12))
                    model.Width = dataReader.GetString(12);
                if (!dataReader.IsDBNull(13))
                    model.Height = dataReader.GetString(13);
                if (!dataReader.IsDBNull(14))
                    model.ButtonLocation = dataReader.GetInt32(14);
                if (!dataReader.IsDBNull(15))
                    model.IsPager = dataReader.GetInt32(15);
                if (!dataReader.IsDBNull(16))
                    model.ClientScript = dataReader.GetString(16);
                if (!dataReader.IsDBNull(17))
                    model.ExportTemplate = dataReader.GetString(17);
                if (!dataReader.IsDBNull(18))
                    model.ExportHeaderText = dataReader.GetString(18);
                if (!dataReader.IsDBNull(19))
                    model.ExportFileName = dataReader.GetString(19);
                if (!dataReader.IsDBNull(20))
                    model.TableStyle = dataReader.GetString(20);
                if (!dataReader.IsDBNull(21))
                    model.TableHead = dataReader.GetString(21);
                if (!dataReader.IsDBNull(22))
                    model.TableName = dataReader.GetString(22);
                if (!dataReader.IsDBNull(23))
                    model.InDataNumberFiledName = dataReader.GetString(23);
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<YJ.Data.Model.ProgramBuilder> GetAll()
        {
            string sql = "SELECT * FROM ProgramBuilder";
            OracleDataReader dataReader = dbHelper.GetDataReader(sql);
            List<YJ.Data.Model.ProgramBuilder> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM ProgramBuilder";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public YJ.Data.Model.ProgramBuilder Get(Guid id)
        {
            string sql = "SELECT * FROM ProgramBuilder WHERE ID=:ID";
            OracleParameter[] parameters = new OracleParameter[]{
				new OracleParameter(":ID", OracleDbType.Varchar2){ Value = id }
			};
            OracleDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.ProgramBuilder> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }

        /// <summary>
        /// 得到分页数据
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="query"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<YJ.Data.Model.ProgramBuilder> GetList(out string pager, string query = "", string name = "", string typeid="")
        {
            List<OracleParameter> parList = new List<OracleParameter>();
            StringBuilder sql = new StringBuilder("SELECT PagerTempTable.*,ROWNUM AS PagerAutoRowNumber FROM (SELECT * FROM ProgramBuilder WHERE Status<>2 ");

            if (!name.IsNullOrEmpty())
            {
                sql.Append(" AND INSTR(Name,:Name,1,1)>0");
                parList.Add(new OracleParameter(":Name", name));
            }

            if (!typeid.IsNullOrEmpty())
            {
                sql.Append(" AND Type IN(" + Utility.Tools.GetSqlInString(typeid) + ")");
            }
            sql.Append(" ORDER BY CreateTime DESC) PagerTempTable");

            long count;
            int size = YJ.Utility.Tools.GetPageSize();
            int number = YJ.Utility.Tools.GetPageNumber();
            string sql1 = dbHelper.GetPaerSql(sql.ToString(), size, number, out count, parList.ToArray());
            pager = YJ.Utility.Tools.GetPagerHtml(count, size, number, query);
            OracleDataReader dataReader = dbHelper.GetDataReader(sql1, parList.ToArray());
            //System.Web.HttpContext.Current.Response.Write(sql1);
            //System.Web.HttpContext.Current.Response.End();
            List<YJ.Data.Model.ProgramBuilder> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
    }
}